﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using bzxxxt.admin.entity;
using bzxxxt.admin.service;
using bzxxxt.dal;
using bzxxxt.model;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;

namespace bzxxxt.bll
{
    public class GraveBLL :BaseBLL
    {
        admin.entity.UserEntity user = admin.service.UserService.GetUser(HttpContext.Current);   //获取cookie里的用户对象
        private string[] GraveTypes = { "","A","B","C"};
        private string[] GraveChars = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" };

        public string getName(string loc)
        {
            if (loc.Length < 3) return "";
            string pcode = loc.Substring(0, 3);
            var p = DB.tbParadise.FirstOrDefault(m => m.code == pcode);
            return p.name.Trim();
        }

        public string getWhere(string whereStr, string status, string paradiseCode,string hourseNum,string floorNum,string sectionNum,string lineNum,string layerNum,string colNum ) {
            if(string.IsNullOrEmpty(whereStr)){
                whereStr = "1=1";
            }
            if (string.IsNullOrEmpty(status) && string.IsNullOrEmpty(paradiseCode) && string.IsNullOrEmpty(hourseNum) &&
                string.IsNullOrEmpty(floorNum) && string.IsNullOrEmpty(sectionNum) && string.IsNullOrEmpty(lineNum) &&
                string.IsNullOrEmpty(layerNum) && string.IsNullOrEmpty(colNum))
            {
                return whereStr;
            }
            
            string locGht = "___";
            string loc2 = "___";
            if (!string.IsNullOrEmpty(status)) {
                if (status == "1") {    //未存放
                    whereStr += " and qty=0";
                } else if (status =="2") {
                    whereStr += " and qty>0";
                }
            }
            paradiseCode = "___" + paradiseCode;
            hourseNum = "__" + hourseNum.Trim();
            floorNum = "__" + floorNum;
            sectionNum = "____" + sectionNum.Trim();
            lineNum = "__" + lineNum.Trim();
            layerNum = "__" + layerNum;
            colNum = "_____" + colNum;
            locGht = paradiseCode.Substring(paradiseCode.Length - 3);
            locGht = locGht + hourseNum.Substring(hourseNum.Length - 1);
            locGht = locGht + floorNum.Substring(floorNum.Length - 1);
            locGht = locGht + sectionNum.Substring(sectionNum.Length - 2);
            locGht = locGht + lineNum.Substring(lineNum.Length - 2);
            locGht = locGht + layerNum.Substring(layerNum.Length - 2);
            locGht = locGht + colNum.Substring(colNum.Length - 3);
            locGht = locGht + "%";

            loc2 = paradiseCode.Substring(paradiseCode.Length - 3);
            loc2 = loc2 + sectionNum.Substring(sectionNum.Length - 4);
            loc2 = loc2 + lineNum.Substring(lineNum.Length - 2);
            loc2 = loc2 + colNum.Substring(colNum.Length - 5);
            loc2 = loc2 + "%";
            if (loc2 == locGht)
            {
                whereStr += " AND (location LIKE '" + loc2 + "')";
            }
            else if(!string.IsNullOrEmpty(paradiseCode))
            {
                try
                {
                    bool isGht = DB.tbParadise.FirstOrDefault(m => m.code == paradiseCode.Substring(paradiseCode.Length - 3)).type == "骨灰堂";
                    if (isGht)
                    {
                        whereStr += " AND (location LIKE '" + locGht + "')";
                    }
                    else
                    {
                        whereStr += " AND (location LIKE '" + loc2 + "')";
                    }
                }
                catch { }
                
            }else{
                whereStr += " AND (location LIKE '" + loc2 + "' OR location LIKE '" + locGht + "')";
            }
            return whereStr;
        }

        public string GetListByPage(int pageSize, int pageIndex, string sort, string where = "1=1", string areacode = null, string deadname = "",  string relativename = "", string relativetel = "",string deadcode = "")
        {
            List<int> relativeIds = new List<int>();
            List<int> graveIds = new List<int>();
            if (!string.IsNullOrEmpty(areacode) || !string.IsNullOrEmpty(relativename) || !string.IsNullOrEmpty(relativetel)) {
                if (areacode.EndsWith("000"))
                {
                    string code = areacode.Substring(0, 9);
                    relativeIds = DB.tbRelative.Where(m => m.name.Contains(relativename) && m.tel.Contains(relativetel) && m.tbArea.code.StartsWith(code)).Select(m => m.id).ToList();
                }
                else {
                    relativeIds = DB.tbRelative.Where(m => m.name.Contains(relativename) && m.tel.Contains(relativetel) && m.tbArea.code.Contains(areacode)).Select(m => m.id).ToList();
                }
                string s = "";
                relativeIds.ForEach(m => { s += m + ","; });
                s = s.Trim(',');
                if (string.IsNullOrEmpty(s)) s = "0";
                where = string.Format("(relativeId in ({0})) and ", s) + where;
            }
            if (!string.IsNullOrEmpty(deadname) || !string.IsNullOrEmpty(deadcode)) {
                if (!string.IsNullOrEmpty(deadname))
                {
                    graveIds = DB.tbDead.Where(m => m.graveid != null && m.name.Contains(deadname)).Select(m => m.graveid.Value).ToList();
                }
                if (graveIds.Count>0 && !string.IsNullOrEmpty(deadcode)) {
                    graveIds =graveIds.Intersect( DB.tbDead.Where(m => m.graveid != null && m.code.Contains(deadcode)).Select(m => m.graveid.Value).ToList()).ToList();
                }
                else if (!string.IsNullOrEmpty(deadcode))
                {
                    graveIds = DB.tbDead.Where(m => m.graveid != null && m.code.Contains(deadcode)).Select(m => m.graveid.Value).ToList();
                }
                string s = "";
                graveIds.ForEach(m => { s += m + ","; });
                s = s.Trim(',');
                if (string.IsNullOrEmpty(s)) s = "0";
                where = string.Format("(id in ({0})) and ", s) + where;
            }
            
            
            int totalCount = 0;

            if (user.roleNames.Contains("镇级管理员") || user.roleNames.Contains("骨灰堂管理员"))
            {
                string s = "";
                user.paradiseIds.ForEach(m => { s += m + ","; });
                s = s.Trim(',');
                where = string.Format("(paradiseId in ({0})) and ", s) + where;
            }


            SqlParameter paramTableName = new SqlParameter("@tableName", "tbGrave");
            SqlParameter paramColumns = new SqlParameter("@columns", "*");
            SqlParameter paramOrder = new SqlParameter("@order", sort);
            SqlParameter paramPageSize = new SqlParameter("@pageSize", pageSize);
            SqlParameter paramPageIndex = new SqlParameter("@pageIndex", pageIndex);
            SqlParameter paramWhere = new SqlParameter("@where", where);
            SqlParameter paramTotalCount = new SqlParameter("@totalCount", totalCount);
            paramTotalCount.Direction = ParameterDirection.Output;
            var p = DB.tbGrave.SqlQuery("exec sp_Pager @tableName,@columns,@order,@pageSize,@pageIndex,@where,@totalCount output",
                new SqlParameter[] {paramTableName, paramColumns, paramOrder, paramPageSize, paramPageIndex, paramWhere, paramTotalCount }).ToList();
            totalCount = Convert.ToInt32(paramTotalCount.Value);
            var list = from g in p
                       select new GraveModel()
                       {
                           id = g.id,
                           name = g.name,
                           location = g.location,
                           graveType = g.graveType,
                           price = g.price,
                           qty = g.qty,
                           hourseNum = g.hourseNum,
                           floorNum = g.floorNum,
                           sectionNum = g.sectionNum,
                           lineNum = g.lineNum,
                           layerNum = g.layerNum,
                           columnNum = g.tbParadise.type=="骨灰堂"? g.columnNum : g.layerNum+g.columnNum,
                           description = g.description,
                           paradiseId = g.paradiseId,
                           paradiseName = g.tbParadise.name,
                           paradiseCode = g.tbParadise.code,
                           relatives = g.relativeId==null?0:1,
                           paidMoney = g.paidMoney,
                           paidTime = g.paidTime,
                           relativeId = g.relativeId,
                           importloc = g.importloc
                       };
            var l = list.OrderBy(m=>m.location).ToList();
            string json = JsonUtil.ToJson(new { total = totalCount, rows = l });
            return json;
        }


        public string Add(tbGrave r, string code)
        {
            var p = DB.tbParadise.FirstOrDefault(m => m.code == code);
            if (p == null)
            {
                return JsonUtil.ToJson(new { success = false, msg = "骨灰堂不存在！" });
            }
            int col = int.Parse(r.columnNum);            
            if (p.type == "骨灰堂")
            {
                char c = Convert.ToChar('A' + r.graveType - 1);
                r.sectionNum = r.sectionNum.Substring(r.sectionNum.Length - 2);
                r.location = string.Format("{0}{1}{2}{3}{4}{5}{6:000}{7}", code, r.hourseNum, r.floorNum, r.sectionNum, r.lineNum, r.layerNum, int.Parse(r.columnNum), c);
                r.name = string.Format("{1}栋{2}楼{3}区{4}排{5}层{6:000}号", code, r.hourseNum, r.floorNum, r.sectionNum, r.lineNum, r.layerNum, int.Parse(r.columnNum));
                r.columnNum = string.Format("{0:000}", int.Parse(r.columnNum));
            }
            else
            {                
                int lay = col / 1000;
                int cn = col % 1000;
                r.floorNum = "0";
                r.layerNum = string.Format("{0:00}", lay);
                r.columnNum = string.Format("{0:000}", cn);
            }
            var g = DB.tbGrave.FirstOrDefault(m => m.location.Substring(0, m.location.Length - 1) == r.location.Substring(0, r.location.Length - 1));
            if (g != null)
            {
                return JsonUtil.ToJson(new { success = false, msg = "[" + g.name + "]穴位已存在，增加失败！" });
            }
            r.paradiseId = p.id;
            DB.tbGrave.Add(r);
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "添加成功!", success = true });
        }
        public string Edit(tbGrave r,string code)
        {
            var p = DB.tbParadise.FirstOrDefault(m => m.code == code);
            if (p == null)
            {
                return JsonUtil.ToJson(new { success = false, msg = "骨灰堂不存在！" });
            }
            int col = int.Parse(r.columnNum);
            if (p.type == "骨灰堂") {
                char c = Convert.ToChar('A' + r.graveType - 1);
                r.sectionNum = r.sectionNum.Substring(r.sectionNum.Length - 2);
                r.location = string.Format("{0}{1}{2}{3}{4}{5}{6:000}{7}", code, r.hourseNum, r.floorNum, r.sectionNum, r.lineNum, r.layerNum, int.Parse( r.columnNum), c);
                r.name = string.Format("{1}栋{2}楼{3}区{4}排{5}层{6:000}号", code, r.hourseNum, r.floorNum, r.sectionNum, r.lineNum, r.layerNum, int.Parse(r.columnNum));
                r.columnNum = string.Format("{0:000}", col);
            }
            else
            {
                int lay = col / 1000;
                int cn = col % 1000;
                r.floorNum = "0";
                r.layerNum = string.Format("{0:00}", lay);
                r.columnNum = string.Format("{0:000}", cn);
            }
            var g = DB.tbGrave.FirstOrDefault(m => m.id == r.id);
            if (g.tbDead.Count > 0)
            {
                return JsonUtil.ToJson(new { success = false, msg = "[" + g.name + "]穴位已存放骨灰，不能修改！" });
            }
            g = DB.tbGrave.FirstOrDefault(m => m.id != r.id && m.location.Substring(0, m.location.Length - 1) == r.location.Substring(0, r.location.Length - 1));
            
            if (g != null)
            {
                return JsonUtil.ToJson(new { success = false, msg = "[" + g.name + "]穴位已存在，修改失败！" });
            }
            
            var d = DB.tbGrave.Where(m => m.id == r.id).FirstOrDefault();
            if (d != null)
            {
                d.location = r.location;
                d.name = r.name;
                d.hourseNum = r.hourseNum;
                d.floorNum = r.floorNum;
                d.sectionNum = r.sectionNum;
                d.lineNum = r.lineNum;
                d.layerNum = r.layerNum;
                d.columnNum = r.columnNum;
                d.graveType = r.graveType;
                d.price = r.price;
                d.paradiseId = p.id;
                d.description = r.description;
            }
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "修改成功!", success = true });
        }
        public string Delete(string graveIds)
        {
            string[] ids = graveIds.Split(',');
            List<int> lstIds = new List<int>();
            foreach (string id in ids) {
                lstIds.Add(int.Parse(id));
            }
            //foreach (var s in ids)
            //{
            //    int id = int.Parse(s);
            //    SqlParameter param = new SqlParameter("@gid", id);
            //    var p = DB.tbGrave.SqlQuery("exec sp_delete_grave_by_id @gid", param).ToList();
            //}
            var mvhs = DB.tbMoveHis.Where(m => (lstIds.Contains(m.fromgraveid) || (m.tograveid!=null && lstIds.Contains(m.tograveid.Value)))).ToList();
            mvhs.ForEach(m => { DB.tbMoveHis.Remove(m); });
            var list = DB.tbGrave.Where(m => lstIds.Contains(m.id)).ToList();
            list.ForEach(m => {
                var d = m.tbDead.ToList();                                
                d.ForEach(dd => m.tbDead.Remove(dd));
                DB.tbGrave.Remove(m);
            });
            DB.SaveChanges();            
            return JsonUtil.ToJson(new { msg = "删除成功!", success = true });
        }

        public string removeDeadFromGrave(int graveId, string deadIds, string remark)
        {
            var grave = DB.tbGrave.FirstOrDefault(m => m.id == graveId);
            if (grave == null)
            {
                throw new Exception("穴位不存在！");
            }
            List<int> lstIds = new List<int>();
            if (!string.IsNullOrEmpty(deadIds))
            {
                string[] ids = deadIds.Split(',');

                foreach (var s in ids)
                {
                    int id = int.Parse(s);
                    lstIds.Add(id);
                }
            }
            var deads = grave.tbDead.ToList();
            deads.ForEach(m =>
            {
                if (lstIds.Count==0 || lstIds.Contains(m.id))
                {
                    m.starttime = null;
                    m.endtime = null;
                    m.relativeid = null;
                    m.tbRelative = null;
                    m.relationid = null;
                    grave.tbDead.Remove(m);

                    //记录
                    tbMoveHis mh = new tbMoveHis
                    {
                        fromgraveid = graveId,
                        userid = user.id,
                        deadid = m.id,
                        movedate = DateTime.Now,
                        remark = remark + "  " + m.name + "[" + m.code + "]"
                    };
                    DB.tbMoveHis.Add(mh);
                }
               
            });
            grave.qty = grave.tbDead.Count();
            if (grave.qty <= 0) {
                grave.relativeId = null;
                grave.tbRelative = null;
                grave.qty = 0;
                grave.paidTime = null;
                grave.paidMoney = null;
            }            
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "移除成功!", success = true });
        }

        public string Move(int oldGraveId, tbGrave newGrave, int deadId,string moveremark)
        {
            var oldGrave = DB.tbGrave.FirstOrDefault(m => m.id == oldGraveId);
            if (oldGrave.tbParadise.type != "骨灰堂")
            {
                string code = newGrave.location.Substring(0, 3);
                int line = int.Parse(newGrave.lineNum);
                int col = int.Parse(newGrave.columnNum);
                string sec = "";
                try
                {
                    sec = string.Format("{0:000}", int.Parse(newGrave.sectionNum));
                    newGrave.location = string.Format("{0}1{1}{2:00}{3:00000}", code, sec, line, col);
                }
                catch
                {
                    sec = newGrave.sectionNum;
                    newGrave.location = string.Format("{0}{1}{2:00}{3:00000}", code, sec, line, col);
                }

                newGrave.name = string.Format("{0}区{1:00}排{2:00000}号", sec, line, col);
            }
            var nGrave = DB.tbGrave.FirstOrDefault(m => m.location.StartsWith(newGrave.location));
            if (nGrave == null)
            {
                return JsonUtil.ToJson(new
                {
                    success = false,
                    msg = string.Format("新的穴位{0}不存在！", newGrave.name)
                });
            }
            if (nGrave.relativeId != null && nGrave.tbDead.Count() >= nGrave.graveType )
            {
                return JsonUtil.ToJson(new
                {
                    success = false,
                    msg = string.Format("新的穴位{0}已满！", nGrave.name)
                });
            }

            if (nGrave.relativeId == null)
            {
                nGrave.relativeId = oldGrave.relativeId;
            }
            
            var dd = oldGrave.tbDead.FirstOrDefault(m=>m.id==deadId);
            oldGrave.tbDead.Remove(dd);
            string dids =  dd.name + "[" + dd.code + "]";
            dd.code = nGrave.location + (nGrave.qty + 1);
            nGrave.qty++;
            nGrave.tbDead.Add(dd);
            nGrave.qty = nGrave.tbDead.Count();
            oldGrave.qty--;
            if (oldGrave.qty <= 0)
            {
                oldGrave.relativeId = null;
                oldGrave.tbRelative = null;
                oldGrave.qty = 0;
                oldGrave.paidTime = null;
                oldGrave.paidMoney = null;
                //oldGrave.description = "迁移到 " + nGrave.tbParadise.name + " " + nGrave.name;
            }
            tbMoveHis mh = new tbMoveHis { 
                deadid = deadId,
                fromgraveid = oldGraveId,
                tograveid = nGrave.id,
                userid = user.id,
                movedate = DateTime.Now,
                remark = moveremark+" "+dids
            };
            DB.tbMoveHis.Add(mh);
            DB.SaveChanges();
            return JsonUtil.ToJson(new
            {
                success = true,
                msg = string.Format("迁移成功！")
            });
        }

        public string Move(int oldGraveId, tbGrave newGrave, string moveremark)
        {
            var oldGrave = DB.tbGrave.FirstOrDefault(m => m.id == oldGraveId);
            if (oldGrave.tbParadise.type != "骨灰堂")
            {
                string code = newGrave.location.Substring(0, 3);
                int line = int.Parse(newGrave.lineNum);
                int col = int.Parse(newGrave.columnNum);
                string sec = "";
                try
                {
                    sec = string.Format("{0:000}", int.Parse(newGrave.sectionNum));
                    newGrave.location = string.Format("{0}1{1}{2:00}{3:00000}", code, sec, line, col);
                }
                catch
                {
                    sec = newGrave.sectionNum;
                    newGrave.location = string.Format("{0}{1}{2:00}{3:00000}", code, sec, line, col);
                }

                newGrave.name = string.Format("{0}区{1:00}排{2:00000}号", sec, line, col);
            }
            var nGrave = DB.tbGrave.FirstOrDefault(m => m.location.StartsWith(newGrave.location));
            if (nGrave == null) {
                return JsonUtil.ToJson(new
                {
                    success = false,
                    msg = string.Format("新的穴位{0}不存在！",newGrave.name)
                });
            }
            if (nGrave.relativeId != null && nGrave.tbDead.Count()>0 ) {
                return JsonUtil.ToJson(new
                {
                    success = false,
                    msg = string.Format("新的穴位{0}已占用！", nGrave.name)
                });
            }

            if (oldGrave.graveType != nGrave.graveType) {
                return JsonUtil.ToJson(new
                {
                    success = false,
                    msg = "两个穴位类型不同，无法迁移！"
                });
            }
            nGrave.relativeId = oldGrave.relativeId;
            nGrave.qty = oldGrave.qty;
            var dd = oldGrave.tbDead.ToList();
            string dids = "";
            int i = 1;
            dd.ForEach(m => {
                dids += "," + m.name + "[" + m.code + "]";
                m.code = nGrave.location + i;
                i++;
                nGrave.tbDead.Add(m);
                oldGrave.tbDead.Remove(m);

                tbMoveHis mh = new tbMoveHis
                {
                    fromgraveid = oldGraveId,
                    tograveid = nGrave.id,
                    userid = user.id,
                    deadid = m.id,
                    movedate = DateTime.Now,
                    remark = moveremark + "  " + dids
                };
                DB.tbMoveHis.Add(mh);
            });


            nGrave.description = oldGrave.tbParadise.name + " "+ oldGrave.name;
            oldGrave.relativeId = null;
            oldGrave.tbRelative = null;
            oldGrave.qty = 0;
            oldGrave.paidTime = null;
            oldGrave.paidMoney = null;
            oldGrave.description = null;            
            DB.SaveChanges();
            return JsonUtil.ToJson(new
            {
                success = true,
                msg = string.Format("迁移成功！")
            });
        }

        public int Import(List<ImportModel> listImport, bool bOutputHint = true)
        {
            HttpResponse Response = HttpContext.Current.Response;
            Dictionary<string, tbRelative> hRelative = new Dictionary<string, tbRelative>();
            //string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
            //StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Append), Encoding.UTF8);
            //DateTime st = DateTime.Now;
            //sw.WriteLine(st.ToString("<br/>开始时间：yyyy-MM-dd HH:mm:ss"));

            StringBuilder sbMsg = new StringBuilder();
            int cnt = 0;
            int total = listImport.Count;
            if (bOutputHint)
            {
                Response.Write("start[" + total + "]<br/>");
                Response.Flush();
            }
            int xx = 0;
            foreach (ImportModel mm in listImport)
            {
                xx++;
                //if (!Cfg.IsRunning) break;
                string[] dnames = mm.dname.Trim().Split(' ', ',', '，', '、', '　');
                string[] dsexes = mm.dsex.Trim().Split(' ', ',', '，', '、', '　');
                for(int xk=0;xk<dnames.Length;xk++)
                //foreach (string dname in dnames)
                {
                    string dname = dnames[xk];
                    string dsex = dsexes[dsexes.Count()-1];
                    if (dsexes.Count() > xk)
                    {
                        dsex = dsexes[xk];
                    }
                    if (dname.Trim().Length == 0) continue; 
                    try
                    {                          
                        cnt++;
                        tbRelative relative = new tbRelative();
                        relative.name = mm.rname;
                        relative.gender = mm.rsex;
                        if (string.IsNullOrEmpty(relative.gender)) relative.gender = "不详";
                        relative.cardno = mm.rcardno;
                        relative.tel = mm.rtel;
                        relative.areacode = mm.cun;
                        relative.addr = mm.zu;

                        tbDead dead = new tbDead();
                        dead.name = dname;
                        dead.relationid = mm.relation;
                        if (string.IsNullOrEmpty(dead.relationid)) dead.relationid = "亲属";

                        dead.storetype = "遗灰";
                        if (!string.IsNullOrEmpty(mm.storeType))
                            dead.storetype = mm.storeType;
                        dead.gender = dsex;
                        dead.starttime = mm.storeTime;
                        if (string.IsNullOrEmpty(dead.gender)) dead.gender = "不详";
                        dead.areacode = mm.cun;
                        dead.homeAddress = mm.zu;
                        int graveId = mm.graveId;
                        if (graveId == 0)
                        {
                            var gv = DB.tbGrave.FirstOrDefault(m => m.location.Substring(0, m.location.Length - 1) == mm.location.Substring(0, mm.location.Length - 1));
                            graveId = gv.id;
                        }
                        tbRelative dr = new tbRelative();
                        string key = relative.name + relative.areacode;
                        //联系人
                        if (!string.IsNullOrEmpty(relative.cardno))
                        {
                            key = relative.name + relative.cardno;
                        }
                        else if (!string.IsNullOrEmpty(relative.tel))
                        {
                            key = relative.name + relative.areacode + relative.tel;
                        }
                        else if (!string.IsNullOrEmpty(relative.addr))
                        {
                            key = relative.name + relative.areacode + relative.addr;
                        }
                        if (!hRelative.ContainsKey(key))
                        {
                            //联系人
                            if (!string.IsNullOrEmpty(relative.cardno))
                            {
                                dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.cardno == relative.cardno);
                            }
                            else if (!string.IsNullOrEmpty(relative.tel))
                            {
                                dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.areacode == relative.areacode && m.tel == relative.tel);
                            }
                            else if (!string.IsNullOrEmpty(relative.addr))
                            {
                                dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.areacode == relative.areacode && m.addr == relative.addr);
                            }
                            else
                            {
                                dr = DB.tbRelative.Where(m => m.name == relative.name && m.areacode == relative.areacode).FirstOrDefault();
                            }
                            //dr = DB.tbRelative.Where(m => m.name == relative.name && m.areacode == relative.areacode).FirstOrDefault(m => (relative.tel.Length > 0 && m.tel == relative.tel) || (relative.cardno.Length > 0 && m.cardno == relative.cardno) || m.addr.Length > 0 && m.addr == relative.addr);

                            if (dr == null)
                            {
                                dr = new tbRelative();
                                dr.name = relative.name;
                                dr.gender = relative.gender;
                                dr.cardno = relative.cardno;
                                dr.tel = relative.tel;
                                dr.areacode = relative.areacode;
                                dr.addr = relative.addr;

                                DB.tbRelative.Add(dr);
                                hRelative.Add(key, dr);

                            }
                            else
                            {
                                dr.name = relative.name;
                                if (relative.gender != "不详")
                                    dr.gender = relative.gender;
                                if (!string.IsNullOrEmpty(relative.cardno))
                                    dr.cardno = relative.cardno;
                                if (!string.IsNullOrEmpty(relative.tel))
                                    dr.tel = relative.tel;
                                if (!string.IsNullOrEmpty(relative.addr))
                                    dr.addr = relative.addr;
                                hRelative.Add(key, dr);
                            }
                        }
                        else
                        {
                            hRelative.TryGetValue(key, out dr);
                        }


                        var grave = DB.tbGrave.FirstOrDefault(m => m.id == graveId);
                        if (grave == null)
                        {
                            string msg = string.Format("<br/>{0}[{1}]穴位不存在！", mm.location, graveId);
                            sbMsg.AppendLine(msg);
                            //sw.WriteLine(msg);
                            continue;
                        }
                        grave.paidTime = mm.regTime;
                        if (grave.relativeId == null)
                        {
                            grave.tbRelative = dr;
                        }

                        //var dd = DB.tbDead.FirstOrDefault(m => m.id == dead.id || (dead.cardno != null && dead.cardno.Length > 0 && m.cardno == dead.cardno) || (dead.areacode != null && m.name == dead.name && m.areacode == dead.areacode) || (dead.homeAddress != null && dead.homeAddress == m.homeAddress && m.name == dead.name));
                        var dd = DB.tbDead.FirstOrDefault(m => (m.name == dead.name && m.code != null && m.code.Contains(grave.location)) || (m.name == dead.name && m.tbRelative.name == relative.name && m.tbGrave.columnNum == grave.columnNum) || m.id == dead.id);
                        if (dd == null)
                        {
                            dd = dead;
                            DB.tbDead.Add(dd);
                        }
                        else
                        {
                            //string msg = string.Format("姓名[{0}]区域[{1}]组[{2}]死者在系统中已经存在！<br/>", dead.name, dd.areacode, dead.homeAddress);
                            //sw.WriteLine(msg);
                            dd.name = dead.name;
                            dd.gender = dead.gender;
                            dd.cardno = dead.cardno;
                            dd.areacode = dead.areacode;
                            dd.actFiretime = dead.actFiretime;
                            dd.starttime = dead.starttime;                            
                            dd.storetype = dead.storetype;
                            dd.relationid = dead.relationid;
                        }

                        //if (dd.graveid == null && grave.qty >= grave.graveType) //为满的穴位            
                        //{
                        //    return JsonUtil.ToJson(new
                        //    {
                        //        success = false,
                        //        msg = string.Format("此穴位已满,寄存失败！")
                        //    });
                        //}
                        //建立穴位与死者的关系    
                        if (dd.graveid != null && dd.graveid != graveId)
                        {
                            string msg = string.Format("序号：{4} 姓名[{0}]村别[{1}]组[{2}]死者已经寄存【{3}】！<br/>", dead.name, dd.tbArea == null ? "" : dd.tbArea.name, dead.homeAddress, "[" + dd.tbGrave.tbParadise.name + "]" + dd.tbGrave.name, mm.id);
                            //sw.WriteLine(msg);
                            sbMsg.AppendLine(msg);
                            continue;
                        }
                        if (dd.graveid == null)
                        {
                            dd.graveid = graveId;
                            grave.qty++;
                            dd.code = grave.location + GraveChars[grave.qty % 10];
                            if (mm.storeTime == null)
                            {
                                dd.starttime = DateTime.Now;
                                dd.endtime = DateTime.Now.AddYears(8);
                            }
                            else
                            {
                                dd.starttime = mm.storeTime;
                                dd.endtime = DateTime.Now.AddYears(8);
                            }
                            if (grave.qty == 1)
                            {
                                grave.description = "[" + dname + "," + mm.oldgrave + "]";
                            }
                            else
                            {
                                grave.description += ",[" + dname + "," + mm.oldgrave + "]";
                            }
                        }
                        if (string.IsNullOrEmpty(dd.code))
                        {
                            dd.code = grave.location + GraveTypes[grave.graveType % 4] + GraveChars[grave.qty % 10];
                        }

                        //建立死者与联系人的关系
                        if (dd.tbRelative == null)
                        {
                            if (dr.id > 0) dd.relativeid = dr.id;
                            else dd.tbRelative = dr;
                        }
                        else
                        {
                            dd.tbRelative.name = relative.name;
                            dd.tbRelative.gender = relative.gender;
                            dd.tbRelative.tel = relative.tel;
                            dd.tbRelative.areacode = relative.areacode;
                            dd.tbRelative.cardno = relative.cardno;
                            dd.tbRelative.addr = relative.addr;
                        }
                        //更新穴位的联系人信息
                        if (grave.qty == 1)
                        {
                            grave.tbRelative.name = relative.name;
                            grave.tbRelative.tel = relative.tel;
                            grave.tbRelative.areacode = relative.areacode;
                            grave.tbRelative.cardno = relative.cardno;
                            grave.tbRelative.addr = relative.addr;
                            grave.tbRelative.gender = relative.gender;

                        }
                        if (cnt % 1 == 0)
                        {
                            if (bOutputHint)
                            {
                                Response.Write("[" + cnt + "] ");
                                Response.Flush();
                            }
                            //string msg = "<br/>[" + cnt + "]";
                            //sbMsg.AppendLine(msg);
                            //sw.WriteLine(msg);
                            //sw.Flush();

                            DB.SaveChanges();

                            hRelative.Clear();
                        }
                    }               
                    catch (Exception ex)
                    {
                        string msg = string.Format("<br/>序号:{0}死者姓名:{1}导入未成功,可能原因:{2}<br/>", mm.id, dname, ex.Message);
                        //sw.WriteLine(msg);
                        sbMsg.AppendLine(msg);
                    }
                }//end for                
            }//end for
            try
            {
                DB.SaveChanges();
            }
            catch (Exception ex) {
                sbMsg.AppendLine(ex.Message);
            }
            if (bOutputHint)
            {
                Response.Write("<br/>end[" + total + "]<br/>");
                Response.Flush();
            }
            if (sbMsg.Length > 0) {
                throw new Exception(sbMsg.ToString());
            }
            //sw.WriteLine("<br/>导入处理数据结束！<br/>");
            //DateTime et = DateTime.Now;
            //TimeSpan ts = et - st;
            //sw.WriteLine(et.ToString("<br/>导入时间：yyyy-MM-dd HH:mm:ss"));
            //sw.WriteLine(string.Format("<br/>耗时:{0}分{1}秒{2}微秒", ts.Minutes, ts.Seconds, ts.Milliseconds));
            //sw.Close();
            return cnt;
        }

        //导入迁移数据
        public void ImportNoHint(List<ImportModel> list)
        {

            StringBuilder sbMsg = new StringBuilder();
            int cnt = 0;
            int total = list.Count;            
            foreach (ImportModel mm in list)
            {
                if (!Cfg.IsRunning)
                {
                    sbMsg.AppendLine("导入模块退出运行...<br/>");
                    break;
                }
                cnt++;
                try
                {
                    tbRelative relative = new tbRelative();
                    relative.name = mm.rname;
                    relative.gender = mm.rsex;
                    if (string.IsNullOrEmpty(relative.gender)) relative.gender = "不详";
                    relative.cardno = mm.rcardno;
                    relative.tel = mm.rtel;
                    relative.areacode = mm.cun;
                    relative.addr = mm.zu;

                    tbDead dead = new tbDead();
                    dead.name = mm.dname;
                    dead.gender = mm.dsex;
                    dead.relationid = mm.relation;
                    if (string.IsNullOrEmpty(dead.relationid)) dead.relationid = "不详";
                    if (!string.IsNullOrEmpty(mm.storeType))
                        dead.storetype = mm.storeType;

                    if (string.IsNullOrEmpty(dead.gender)) dead.gender = "不详";
                    dead.areacode = mm.cun;
                    dead.homeAddress = mm.zu;

                    int graveId = mm.graveId;

                    //联系人
                    var dr = DB.tbRelative.FirstOrDefault(m => (m.name == relative.name && m.tel == relative.tel) || (relative.areacode != null && m.areacode == relative.areacode && m.name == relative.name) || (relative.addr != null && relative.addr == m.addr && relative.name == m.name) || (relative.cardno != null && relative.cardno.Length > 0 && m.cardno == relative.cardno));
                    if (dr == null)
                    {
                        dr = new tbRelative();
                        dr.name = relative.name;
                        dr.gender = relative.gender;
                        dr.cardno = relative.cardno;
                        dr.tel = relative.tel;
                        dr.areacode = relative.areacode;
                        DB.tbRelative.Add(dr);
                    }
                    else
                    {
                        dr.name = relative.name;
                        dr.gender = relative.gender;
                        dr.cardno = relative.cardno;
                        dr.tel = relative.tel;
                        dr.areacode = relative.areacode;
                    }

                    var grave = DB.tbGrave.FirstOrDefault(m => m.id == graveId);
                    if (grave == null)
                    {
                        sbMsg.AppendFormat("{0}[{1}]穴位不存在！", mm.location, graveId);
                        sbMsg.AppendLine();
                        continue;
                    }
                    grave.paidTime = mm.regTime;
                    if (grave.relativeId == null)
                    {
                        grave.tbRelative = dr;
                    }

                    //var dd = DB.tbDead.FirstOrDefault(m => m.id == dead.id || (dead.cardno != null && dead.cardno.Length > 0 && m.cardno == dead.cardno) || (dead.areacode != null && m.name == dead.name && m.areacode == dead.areacode) || (dead.homeAddress != null && dead.homeAddress == m.homeAddress && m.name == dead.name));
                    var dd = DB.tbDead.FirstOrDefault(m => m.id == dead.id || (m.name == dead.name && m.tbRelative.name == relative.name && m.tbGrave.columnNum == grave.columnNum));
                    if (dd == null)
                    {
                        dd = dead;
                        DB.tbDead.Add(dd);
                    }
                    else
                    {
                        sbMsg.AppendFormat("姓名[{0}]区域[{1}]组[{2}]死者在系统中已经存在！<br/>", dead.name, dd.tbArea!=null?dd.tbArea.name:"", dead.homeAddress);
                        dd.name = dead.name;
                        dd.gender = dead.gender;
                        dd.cardno = dead.cardno;
                        dd.areacode = dead.areacode;
                        dd.actFiretime = dead.actFiretime;
                        dd.storetype = dead.storetype;
                        dd.relationid = dead.relationid;
                    }

                    //if (dd.graveid == null && grave.qty >= grave.graveType) //为满的穴位            
                    //{
                    //    return JsonUtil.ToJson(new
                    //    {
                    //        success = false,
                    //        msg = string.Format("此穴位已满,寄存失败！")
                    //    });
                    //}
                    //建立穴位与死者的关系    
                    if (dd.graveid != null && dd.graveid != graveId)
                    {
                        sbMsg.AppendFormat("姓名[{0}]区域[{1}]组[{2}]死者已经寄存【{3}】<br/>", dead.name, dd.tbArea!=null?dd.tbArea.name:"", dead.homeAddress,"["+dd.tbGrave.tbParadise.name + "]"+dd.tbGrave.name);

                        continue;
                    }
                    if (dd.graveid == null)
                    {
                        dd.graveid = graveId;
                        grave.qty++;
                        dd.code = grave.location + GraveChars[grave.qty % 10];
                        if (mm.storeTime == null)
                        {
                            dd.starttime = DateTime.Now;
                            dd.endtime = DateTime.Now.AddYears(8);
                        }
                        else
                        {
                            dd.starttime = mm.storeTime;
                            dd.endtime = DateTime.Now.AddYears(8);
                        }
                        if (grave.qty == 1)
                        {
                            grave.description = "[" + mm.dname + "," + mm.oldgrave + "]";
                        }
                        else
                        {
                            grave.description += ",[" + mm.dname + "," + mm.oldgrave + "]";
                        }
                    }
                    if (string.IsNullOrEmpty(dd.code))
                    {
                        dd.code = grave.location + GraveTypes[grave.graveType % 4] + GraveChars[grave.qty % 10];
                    }

                    //建立死者与联系人的关系
                    if (dd.tbRelative == null)
                    {
                        if (dr.id > 0) dd.relativeid = dr.id;
                        else dd.tbRelative = dr;
                    }
                    else
                    {
                        dd.tbRelative.name = relative.name;
                        dd.tbRelative.tel = relative.tel;
                        dd.tbRelative.areacode = relative.areacode;
                        dd.tbRelative.cardno = relative.cardno;
                    }
                    //更新穴位的联系人信息
                    if (grave.qty == 1)
                    {
                        grave.tbRelative.name = relative.name;
                        grave.tbRelative.tel = relative.tel;
                        grave.tbRelative.areacode = relative.areacode;
                        grave.tbRelative.cardno = relative.cardno;

                    }
                    DB.SaveChanges();
                }
                catch (Exception ex)
                {
                    sbMsg.AppendFormat("序号:{0}死者姓名:{1}导入未成功,可能原因:{2}<br/>", mm.id, mm.dname, ex.Message);
                }                

            }
            if (sbMsg.Length > 0)
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.Append);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("部分数据导入失败:<br/>");
                sw.WriteLine(sbMsg.ToString());
                sw.Close();
                throw new Exception("/admin/upload/log.html");
            }
            else
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.Append);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("导入处理数据成功！<br/>");
                sw.Close();
                
            }            
        }



        /// <summary>
        /// 读取excel,导入迁移数据
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public List<ImportModel> ImportExceltoDb(UserEntity user, string strFileName, int SheetIndex, int HeaderRowIndex, bool autocreate = false)
        {
            if (user.id != 1)
            {
                if (user.paradiseIds == null || user.paradiseIds.Count <= 0)
                {
                    throw new Exception("没有导入权限!");
                }
            }
            List<string> pnames = DB.tbParadise.Where(m => user.paradiseIds.Any(id => id == m.id)).Select(m => m.name).ToList();
            List<ImportModel> list = new List<ImportModel>();
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);            
            //table = ImportDt(sheet, HeaderRowIndex, needHeader);

            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            StringBuilder sbMsg = new StringBuilder();
            StringBuilder sbLine = new StringBuilder();
            try
            {
                //检查表格的标题是否符合模板格式
                
                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                string[] headers = { "序号", "村别", "户主", "组别", "联系电话", "亡者姓名", "性别", "原存放穴位", "骨灰堂", "栋", "楼", "区", "排", "层", "列", "穴位类型", "关系" };
                if (cellCount < headers.Length)
                {
                    throw new Exception("数据文件格式列数与模板列数不符！");
                }
                for (int i = 0; i < headers.Length; i++)
                {
                    string headtitle = headerRow.GetCell(i).ToString();
                    if (headers[i] != headtitle) {
                        throw new Exception("数据文件第"+i+"列名称与数据模板不符！");
                    }                    
                }
                int rowCount = sheet.LastRowNum;

                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {                    
                    ImportModel m = new ImportModel();
                    sbLine.Clear();
                    try
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            sbMsg.AppendFormat("第{0}行无数据!<br/>", i + 1);
                            continue;
                        }
                        //先处理创建穴位
                        string paradise = getCellVaue(row.GetCell(8));
                        //处理骨灰堂
                        var p = DB.tbParadise.FirstOrDefault(mp => mp.name == paradise);
                        if (p == null)
                        {
                            string msg = string.Format("第{0}行骨灰堂【{1}】无效!<br/> ", i + 1, paradise);
                            throw new Exception(msg);
                        }
                        if (user.id != 1)
                        {
                            var s = pnames.FirstOrDefault(n => n == p.name);
                            if (s == null || s.Count() == 0)
                            {
                                throw new Exception("没有访问[" + p.name + "]的权限！");
                            }
                        }
                        m.pcode = p.code;

                        string dong = getCellVaue(row.GetCell(9));
                        m.dong = int.Parse(dong);

                        string floor = getCellVaue(row.GetCell(10));
                        m.floor = int.Parse(floor);

                        m.SectionStr = getCellVaue(row.GetCell(11));
                        m.SectionStr = Utils.getSectionStr(m.SectionStr);
                        
                        m.LineStr = "0" + getCellVaue(row.GetCell(12));
                        m.LineStr = m.LineStr.Substring(m.LineStr.Length - 2);

                        string layer = getCellVaue(row.GetCell(13));
                        m.layer = int.Parse(layer);

                        string col = getCellVaue(row.GetCell(14));
                        m.col = int.Parse(col);

                        string graveType = getCellVaue(row.GetCell(15));
                        if (graveType.IndexOf("单") >= 0)
                        {
                            m.gtype = 1;
                        }
                        else if (graveType.IndexOf("双") >= 0)
                        {
                            m.gtype = 2;
                        }
                        else
                        {
                            m.gtype = 3;
                        }
                        string section = m.SectionStr.Substring(m.SectionStr.Length - 2);
                        string loc = m.location;
                        string locName = string.Format("{1}栋{2}楼{3:00}区{4}排{5:00}层{6:000}号", m.pname, m.dong, m.floor, section, m.LineStr, m.layer, m.col);
                        if (p.type != "骨灰堂") //如果不是骨灰堂
                        {
                            char cc = Convert.ToChar('A' + m.gtype - 1);
                            loc = string.Format("{0:000}{1}{2}{3:00000}{4:0}", m.pcode, Utils.getSectionStr4Bit(m.SectionStr), m.LineStr, m.col, cc);
                            locName = string.Format("{0}区{1}排{2:00000}号", m.SectionStr, m.LineStr, m.col);
                            section = m.SectionStr;
                            floor = "0";
                            m.layer = m.col / 1000 % 100;
                            m.col = m.col % 1000;
                        }
                        if (loc.Length != 15) {
                            sbLine.AppendFormat("第{0}行存放位置{1}不正确! ", i + 1, loc);
                            continue;
                        }
                        var g = DB.tbGrave.FirstOrDefault(mg => mg.location.Substring(0, m.location.Length - 1) == loc.Substring(0, loc.Length - 1));
                        if (g == null)
                        {
                            if (!autocreate)
                            {
                                sbLine.AppendFormat("第{0}行存放位置{1}不存在! ", i + 1, loc);
                            }
                            else
                            {
                                g = new tbGrave
                                {
                                    location = loc,
                                    name = locName,
                                    hourseNum = dong,
                                    floorNum = floor,
                                    sectionNum = section,
                                    lineNum = m.LineStr,
                                    layerNum = string.Format("{0:00}", m.layer),
                                    columnNum = string.Format("{0:000}", m.col),
                                    qty = 0,
                                    graveType = m.gtype
                                };

                                var pd = DB.tbParadise.FirstOrDefault(pr => pr.code == m.pcode);
                                g.paradiseId = pd.id;
                                DB.tbGrave.Add(g);
                                DB.SaveChanges();
                                m.graveId = g.id;
                                //sbLine.AppendFormat("第{0}行存放位置{1}不存在,已经创建【{2}】 ", i + 1, loc,g.id);
                            }
                        }
                        else
                        {
                            m.graveId = g.id;
                        }
                        string relation = getCellVaue(row.GetCell(16));
                        m.relation = relation;
                        try
                        {
                            string datestr = getCellVaue(row.GetCell(17));
                            m.storeTime = DateTime.Parse(datestr);
                        }
                        catch
                        {
                            m.storeTime = DateTime.Now;
                        }
                        

                        
                        string id = getCellVaue(row.GetCell(0));
                        m.id = int.Parse(id);
                        string cun = getCellVaue(row.GetCell(1));
                        if (cun.Length == 0) {
                            cun = "不详";
                        }
                        string rname = getCellVaue(row.GetCell(2));
                        string zu = getCellVaue(row.GetCell(3));
                        m.zu = zu;

                        string rtel = getCellVaue(row.GetCell(4));
                        m.rtel = rtel;
                        if (m.rtel.Length == 0) {
                            m.rtel = "无";
                        }

                        string dname = getCellVaue(row.GetCell(5));
                        if (string.IsNullOrEmpty(dname))
                        {
                            sbLine.AppendFormat("第{0}行死者姓名为空,只创建穴位【{1}】! ", i + 1,m.location);
                            continue;
                        }
                        else
                        {
                            m.dname = dname;
                        }     
                        string dsex = getCellVaue(row.GetCell(6));
                        m.dsex = dsex;

                        string grave = getCellVaue(row.GetCell(7));
                        m.oldgrave = grave;
                        //处理村名
                        var c = DB.tbArea.FirstOrDefault(area => area.name.IndexOf(cun)>=0);
                        if (c == null)
                        {
                            sbLine.AppendFormat("第{0}行村别【{1}】无效! ", i + 1, cun);                            
                        }
                        else
                        {
                            m.cun = c.code;
                        }
                        
                        if (string.IsNullOrEmpty(rname))
                        {
                            //sbLine.AppendFormat("第{0}行户主姓名不能为空! ", i + 1);
                            m.rname = "无";
                        }
                        else
                        {
                            m.rname = rname;
                        }
                        if (m.rname.Length >= 10)
                        {
                            sbLine.AppendFormat("第{0}行户主姓名【{1}】名字太长! ", i + 1, m.rname);        
                        }
                    }
                    catch (Exception ex1)
                    {
                        sbLine.AppendFormat(" 第{0}行数据有错误:【" + ex1.Message+"】", i + 1);
                    }

                    if (sbLine.Length > 0)
                    {
                        sbMsg.AppendLine(sbLine.ToString());
                        sbLine.Clear();
                    }
                    else
                    {
                        list.Add(m);
                    }
                } //end for                
            }
            catch (Exception ex)
            {
                sbMsg.Append("解析数据失败:<br/>" + ex.Message+":"+ex.InnerException);
                //string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                //FileStream fs = new FileStream(path, FileMode.Create);
                //StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);                
                //sw.WriteLine(sbMsg.ToString());
                //sw.Close();
                throw new Exception(sbMsg.ToString());
            }
            //if (sbMsg.Length > 0)
            //{
            //    string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
            //    FileStream fs = new FileStream(path, FileMode.Create);
            //    StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
            //    sw.WriteLine("解析数据出错：<br/>");
            //    sw.WriteLine(sbMsg.ToString());
            //    sw.Close();
            //    throw new Exception(sbMsg.ToString());
            //}
            //else
            //{
            //    string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
            //    FileStream fs = new FileStream(path, FileMode.Create);
            //    StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
            //    sw.WriteLine("解析数据成功！准备导入...<br/>");
            //    sw.Close();
            //}
            if (sbMsg.Length > 0)
            {                
                throw new Exception(sbMsg.ToString());
            }
            
            sheet = null;
            return list;
        }

        private string getCellVaue(ICell cell)
        {
            if (cell == null) return "";
            string v = "";
            switch (cell.CellType)
            {
                case CellType.String:
                    v = cell.StringCellValue;

                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        DateTime dt = DateTime.FromOADate(cell.NumericCellValue);
                        v = dt.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        v = Convert.ToString(cell.NumericCellValue);
                    }
                    break;
                case CellType.Boolean:
                    v = Convert.ToString(cell.BooleanCellValue);
                    break;
                case CellType.Error:
                    v = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    v = "";
                    break;
            }
            return v.Trim();
        }

        public string Store(tbDead dead, tbRelative relative, int graveId, bool bSave=true,bool bEditRelative=false)
        {            
            //联系人
            tbRelative dr = null;
            if (relative.id > 0 && dead.id > 0) {  //修改寄存时
                dr = DB.tbRelative.FirstOrDefault(m => m.id == relative.id);
                if (bEditRelative)
                {
                    if (dr.name != relative.name)
                    {
                        dr = null;
                    }
                }
            }
            if (dr != null)
            {
            }
            else if (!string.IsNullOrEmpty(relative.cardno))
            {
                dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.areacode == relative.areacode && m.cardno == relative.cardno);
            }
            else if (!string.IsNullOrEmpty(relative.tel))
            {
                dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.areacode == relative.areacode && m.tel == relative.tel);
            }
            if (dr == null)
            {
                dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.areacode == relative.areacode && String.IsNullOrEmpty(m.tel) && String.IsNullOrEmpty(m.cardno));

                if (dr!=null)
                {                    
                    dr.name = relative.name;
                    dr.cardno = relative.cardno;
                    dr.tel = relative.tel;
                    dr.areacode = relative.areacode;
                }
                else
                {
                    dr = new tbRelative();
                    dr.name = relative.name;
                    dr.cardno = relative.cardno;
                    dr.tel = relative.tel;
                    dr.areacode = relative.areacode;
                    DB.tbRelative.Add(dr);
                }
            }
            else
            {
                dr.name = relative.name;
                dr.cardno = relative.cardno;
                dr.tel = relative.tel;
                dr.areacode = relative.areacode;
            }
            
            var grave = DB.tbGrave.FirstOrDefault(m => m.id == graveId);
            if (grave == null) {
                return JsonUtil.ToJson(new
                {
                    success = false,
                    msg = string.Format("穴位不存在！")
                });
            }
            if (grave.relativeId == null || grave.relativeId!=dr.id)
            {
                grave.tbRelative = dr;
                grave.relativeId = dr.id;
            }
            
            tbDead dd = null;
            if (dead.id > 0) {
                dd = DB.tbDead.FirstOrDefault(m => m.id == dead.id);
            }
            else if (!string.IsNullOrEmpty(dead.cardno))
            {
                dd = DB.tbDead.FirstOrDefault(m => m.id == dead.id || (dead.cardno != null && dead.cardno.Length > 0 && m.cardno == dead.cardno) || (m.name == dead.name && m.areacode == dead.areacode && dead.homeAddress == m.homeAddress));

            }
            else {
                dd = DB.tbDead.FirstOrDefault(m=>(m.graveid==null || m.graveid==0) && m.name == dead.name && m.areacode == dead.areacode);
            }
            
            if (dd == null)
            {
                dd = dead;
                DB.tbDead.Add(dd);
            }
            else
            {
                dd.name = dead.name;
                dd.gender = dead.gender;
                dd.cardno = dead.cardno;
                dd.areacode = dead.areacode;
                dd.actFiretime = dead.actFiretime;
                dd.storetype = dead.storetype;
                dd.relationid = dead.relationid;
                dd.starttime = dead.starttime;
                dd.description = dead.description;
                dd.homeAddress = dead.homeAddress;
            }
                                  
            //if (dd.graveid == null && grave.qty >= grave.graveType) //为满的穴位            
            //{
            //    return JsonUtil.ToJson(new
            //    {
            //        success = false,
            //        msg = string.Format("此穴位已满,寄存失败！")
            //    });
            //}
            //建立穴位与死者的关系    
            if (dd.graveid != null && dd.graveid != graveId)
            {
                return JsonUtil.ToJson(new
                {
                    success = false,
                    msg = string.Format("此死者已寄存！")
                });
            }
            if (dd.graveid == null)
            {
                dd.graveid = graveId;
                grave.qty++;
                dd.code = grave.location + GraveChars[grave.qty % 10];
                if (bSave)
                {
                    //dd.starttime = DateTime.Now;
                    dd.endtime = DateTime.Now.AddYears(8);
                }
                else
                {
                    dd.estStoretime = dd.starttime;
                    dd.starttime = null;
                    dd.endtime = null;
                }
            }
            else {
                dd.code = grave.location + GraveChars[grave.qty % 10];
                if (bSave)
                {
                    if (dd.starttime == null)
                    {
                        dd.starttime = DateTime.Now;
                        dd.endtime = DateTime.Now.AddYears(8);
                    }
                }
                else
                {
                    dd.starttime = null;
                    dd.endtime = null;
                }
            }
            if (string.IsNullOrEmpty(dd.code))
            {
                dd.code = grave.location + GraveChars[grave.qty % 10];
            }

            //建立死者与联系人的关系
            if (dd.tbRelative == null || dr.id == 0 || dd.tbRelative.id!=dr.id)
            {
                if (dr.id > 0)
                {
                    dd.relativeid = dr.id;
                    dd.tbRelative = dr;
                }
                else dd.tbRelative = dr;                                     
            }
            else
            {
                dd.tbRelative.name = relative.name;
                dd.tbRelative.tel = relative.tel;
                dd.tbRelative.areacode = relative.areacode;
                dd.tbRelative.cardno = relative.cardno;
            }
            //更新穴位的联系人信息
            if (grave.qty == 1) {
                grave.tbRelative.name = relative.name;
                grave.tbRelative.tel = relative.tel;
                grave.tbRelative.areacode = relative.areacode;
                grave.tbRelative.cardno = relative.cardno;
            }
            
            DB.SaveChanges();
            grave.qty = grave.tbDead.Count;
            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "存储骨灰成功!", success = true });
        }

        public string Sale(int relativeId, int graveId, float paid)
        {
            var dd = DB.tbGrave.FirstOrDefault(m => m.id == graveId);
            if (dd.relativeId!=null && dd.relativeId!=relativeId)
            {
                return JsonUtil.ToJson(new
                {
                    success = false,
                    msg = string.Format("该穴位已登记!")
                });
            }
            if (dd.relativeId == null)
            {
                //建立穴位与联系人的关系       
                dd.relativeId = relativeId;
                dd.saleTime = DateTime.Now;
            }
            if (dd.paidMoney != paid)
            {
                dd.paidMoney = paid;
                dd.paidTime = DateTime.Now;
            }
            
            //DB.tbGraveRelative.Add(new tbGraveRelative
            //{
            //    graveId = graveId,
            //    relativeId = relativeId,
            //    paidMoney = paid,
            //    saleTime = DateTime.Now,
            //    paidTime = DateTime.Now
            //});

            DB.SaveChanges();
            return JsonUtil.ToJson(new { msg = "销售穴位成功!", success = true });
        }

        public List<DeadModel> QueryList(string loc) {
            var list = DB.tbDead.Where(m =>m.graveid!=null && m.code.StartsWith(loc)).Select(d => new DeadModel() {
                id = d.id,
                name = d.name,
                cardno = d.cardno,
                code = d.code,
                gender = d.gender,
                birthday = d.birthday,
                deathtime = d.deathtime,
                nationId = d.nationid,
                nationName = d.nationid,
                areacode = d.areacode,
                areaName = d.tbArea == null ? "" : d.tbArea.name,
                profession = d.professionid,
                education = d.educationid,
                deathcause = d.educationid,
                deathplace = d.deathplace,
                birAddress = d.birthAddress,
                homeAddress = d.homeAddress,
                photo = d.photo == null ? "/admin/images/dead.jpg" : d.photo,
                estArriveplace = d.estArriveplace,
                estArrivetime = d.estArrivetime,
                actFiretime = d.actFiretime,
                estStoretime = d.estStoretime,
                estStoreplace = d.estStoreplace,
                relativeId = d.relativeid,
                relationId = d.relationid,
                relativeName = d.tbRelative == null ? "" : d.tbRelative.name,
                relativeTel = d.tbRelative == null ? "" : d.tbRelative.tel,
                graveName = d.tbGrave == null ? "" : "[" + d.tbGrave.tbParadise.name + "]" + d.tbGrave.name,
                storeTime = d.starttime     
            }).ToList(); ;
            return list;
        }

        public string getNavTree(int id = 0, string code = "") {
            
            List<NavTreeNode> treeList = new List<NavTreeNode>();
            if (string.IsNullOrEmpty(code) || code.Length<3)
                return JsonUtil.ToJson(treeList);
            var mm = DB.tbParadise.First(m => m.code == code.Substring(0,3));
            int n = code.Length;
            if (mm.type == "骨灰堂")
            {
                switch (n)
                {
                    case 3:     //堂,显示楼层
                        {
                            //楼层
                            var floorsdata = DB.tbGrave.Where(g => g.location.StartsWith(code));
                            var floors = floorsdata.Select(g => new { g.hourseNum, g.floorNum }).Distinct().OrderBy(g => g.hourseNum+g.floorNum).ToList();
                            floors.ForEach(ff =>
                            {
                                NavTreeNode tnf = new NavTreeNode();
                                tnf.id = id * 100 + int.Parse(ff.hourseNum) * 10 + int.Parse(ff.floorNum);
                                tnf.code = code + ff.hourseNum + ff.floorNum;
                                tnf.text = ff.hourseNum + "栋" + ff.floorNum + "楼";
                                tnf.iconCls = "icon-floor";
                                tnf.isGht = true;
                                tnf.state = "closed";
                                treeList.Add(tnf);
                            });

                            break;
                        }
                    case 5:     //楼层，显示区域
                        {
                            //区域
                            var sectionsdata = DB.tbGrave.Where(g => g.location.StartsWith(code));
                            var sections = sectionsdata.Select(f => f.sectionNum).Distinct().OrderBy(f => f).ToList();
                            sections.ForEach(ss =>
                            {
                                NavTreeNode tns = new NavTreeNode();
                                tns.id = id * 100 + int.Parse(ss);
                                tns.code = code + ss;
                                tns.text = ss + "区";
                                tns.isGht = true;
                                tns.iconCls = "icon-section";
                                tns.state = "closed";
                                treeList.Add(tns);
                            });

                            break;
                        }
                    case 7:     //区
                        {
                            //排
                            var linesdata = DB.tbGrave.Where(g => g.location.StartsWith(code));
                            var lines = linesdata.Select(s => s.lineNum).Distinct().OrderBy(line => line).ToList();
                            lines.ForEach(ll =>
                            {
                                NavTreeNode tnl = new NavTreeNode();
                                tnl.id = id * 100 + int.Parse(ll);
                                tnl.code = code + ll;
                                tnl.text = ll + "排";
                                tnl.isGht = true;
                                tnl.iconCls = "icon-line";
                                tnl.state = "closed";
                                treeList.Add(tnl);
                            });

                            break;
                        }
                    case 9:     //排
                        {
                            //层
                            var layersdata = DB.tbGrave.Where(g => g.location.StartsWith(code));
                            var layers = layersdata.Select(lay => lay.layerNum).Distinct().OrderBy(lay => lay).ToList();
                            layers.ForEach(lay =>
                            {
                                NavTreeNode tnlay = new NavTreeNode();
                                tnlay.id = id * 10 + int.Parse(lay);
                                tnlay.code = code + lay;
                                tnlay.text = lay + "层";
                                tnlay.isGht = true;
                                tnlay.iconCls = "icon-layer";
                                tnlay.state = "closed";
                                treeList.Add(tnlay);
                            });

                            break;
                        }
                    case 11:    //层
                        {
                            var colsdata = DB.tbGrave.Where(g => g.location.StartsWith(code))
                                .Select(col => col.columnNum).OrderBy(col => col).ToList();
                            colsdata.ForEach(col =>
                            {
                                NavTreeNode tncol = new NavTreeNode();
                                tncol.id = id * 1000 + int.Parse(col);
                                tncol.code = code + col;
                                tncol.text = col + "号";
                                tncol.isGht = true;
                                tncol.iconCls = "icon-layer";
                                
                                treeList.Add(tncol);
                            });
                            break;
                        }
                }
            }
            else {
                Random rdm = new Random();
                switch (n)
                {
                    case 3:     //堂，显示区
                        {

                            //区域
                            var sectionsdata = DB.tbGrave.Where(f => f.location.StartsWith(code));
                            var sections = sectionsdata.Select(f => new { f.hourseNum, f.sectionNum }).Distinct().OrderBy(f => f.sectionNum).ToList();
                            
                            sections.ForEach(ss =>
                            {
                                NavTreeNode tns = new NavTreeNode();

                                string sec = ss.sectionNum;
                                if (ss.sectionNum.Length !=4)
                                {
                                    sec = Utils.getSectionStr(ss.sectionNum);
                                    tns.id = id * 1000 + int.Parse(ss.hourseNum) * 100 + rdm.Next(100);
                                    tns.code = code + ss.hourseNum + sec;
                                }
                                else {
                                    tns.id = id * 1000 + int.Parse(ss.hourseNum) * 100 + rdm.Next(100);
                                    tns.code = code +  sec;
                                }
                                //tns.text = ss.hourseNum + "栋" + sec + "区";
                                tns.text = sec + "区";
                                tns.isGht = false;
                                tns.iconCls = "icon-section";
                                tns.state = "closed";
                                treeList.Add(tns);
                            });
                            break;
                        }
                    //case 5:     //楼层
                    //    break;
                    case 7:     //区
                        {
                            //排
                            var linesdata = DB.tbGrave.Where(f => f.location.StartsWith(code));
                            var lines = linesdata.Select(s => s.lineNum).Distinct().OrderBy(s => s).ToList();
                            lines.ForEach(ll =>
                            {
                                NavTreeNode tnl = new NavTreeNode();
                                //tnl.id = id * 100 + int.Parse(ll);
                                tnl.id = id * 100 + rdm.Next(100);
                                tnl.code = code + ll;
                                tnl.text = ll + "排";
                                tnl.isGht = false;
                                tnl.iconCls = "icon-line";
                                tnl.state = "closed";
                                treeList.Add(tnl);
                            });

                            break;
                        }
                    case 9:     //排
                        {
                            var colsdata = DB.tbGrave.Where(f => f.location.StartsWith(code)).Select(col => col.layerNum + col.columnNum).OrderBy(col => col).ToList();
                            colsdata.ForEach(col =>
                            {
                                NavTreeNode tncol = new NavTreeNode();
                                tncol.id = id * 1000 + int.Parse(col);
                                tncol.code = code + col;
                                tncol.text = col + "号";
                                tncol.isGht = false;
                                tncol.iconCls = "icon-layer";
                                treeList.Add(tncol);
                            });
                            break;
                        }
                    //case 11:    //层

                    //    break;
                }
            }
            return JsonUtil.ToJson(treeList);
        }

        public string getNavTopTree()
        {
            List<NavTreeNode> treeList = new List<NavTreeNode>();
            List<tbParadise> list = new List<tbParadise>();

            if (user.roleNames.Contains("镇级管理员") || user.roleNames.Contains("骨灰堂管理员"))
            {
                list = DB.tbParadise.Where(m => user.paradiseIds.Contains(m.id)).ToList();
            }
            else
            {
                list = DB.tbParadise.OrderBy(m => m.code).ToList();
            }
            //只生成顶层树
            list.ForEach(m =>
            {
                NavTreeNode tnode = new NavTreeNode();
                tnode.id = m.id;
                tnode.text = m.name;
                tnode.code = m.code;
                tnode.state = "closed";
                if (m.type == "骨灰堂")
                {
                    tnode.iconCls = "icon-grave";
                    tnode.isGht = true;
                }
                else
                {
                    tnode.iconCls = "icon-grave2";
                    tnode.isGht = false;
                }
                treeList.Add(tnode);
            });
            return JsonUtil.ToJson(treeList);
        }       
        
    }
}